﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace Speedy
{
    public class DataLayer
    {
        static DataLayer instance;
        private DataLayer()
        {

        }
        public static DataLayer GetLayer
        {
            get
            {
                if (instance == null)
                    instance = new DataLayer();
                return instance;
            }
        }
        public string CnnString { get; set; }

        public int ExecQuery(string Query)
        {
            int result = 0;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand("", cn))
                {
                    try
                    {
                        cn.Open();
                        if (Query.IndexOf(';') > -1)
                            foreach (string sql in Query.Split(';'))
                            {
                                cmd.CommandText = sql;
                                result = cmd.ExecuteNonQuery();
                            }
                        else
                        {
                            cmd.CommandText = Query;
                            result = cmd.ExecuteNonQuery();
                        }
                        cn.Close();
                    }
                    catch (Exception ex)
                    {
                        Logging.GetLog.ErrorLog(ex);
                    }
                }
            }
            return result;
        }
        public int ExecQuery(string Query, params SqlParameter[] prm)
        {
            int result = 0;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand(Query, cn))
                {
                    try
                    {
                        cn.Open();
                        foreach (SqlParameter p in prm)
                            cmd.Parameters.Add(p);
                        result = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        cn.Close();
                    }
                    catch (Exception ex)
                    {
                        Logging.GetLog.ErrorLog(ex);
                    }
                }
            }
            return result;
        }
        public int ExecQuery(string Query, SqlTransaction trn, params SqlParameter[] prm)
        {
            int result = 0;
            SqlConnection cn = trn.Connection;
            using (SqlCommand cmd = new SqlCommand(Query, cn))
            {
                try
                {
                    cmd.Transaction = trn;
                    if (cn.State == ConnectionState.Closed) cn.Open();
                    foreach (SqlParameter p in prm)
                        cmd.Parameters.Add(p);
                    result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logging.GetLog.ErrorLog(ex);
                }
            }
            return result;
        }

        public object ExecScalar(string Query)
        {
            object result = null;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand("", cn))
                {
                    try
                    {
                        cn.Open();
                        if (Query.IndexOf(';') > -1)
                            foreach (string sql in Query.Split(';'))
                            {
                                cmd.CommandText = sql;
                                result = cmd.ExecuteScalar();
                            }
                        else
                        {
                            cmd.CommandText = Query;
                            result = cmd.ExecuteScalar();
                        }
                        cn.Close();
                    }
                    catch (Exception ex)
                    {
                        Logging.GetLog.ErrorLog(ex);
                    }
                }
            }
            return result;
        }
        public object ExecScalar(string Query, params SqlParameter[] prm)
        {
            object result = 0;
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand(Query, cn))
                {
                    try
                    {
                        cn.Open();
                        foreach (SqlParameter p in prm)
                            cmd.Parameters.Add(p);
                        result = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        cn.Close();
                    }
                    catch (Exception ex)
                    {
                        Logging.GetLog.ErrorLog(ex);
                    }
                }
            }
            return result;
        }

        public DataTable FillData(string Query)
        {
            DataTable result = new DataTable();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand(Query, cn))
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        try
                        {
                            cn.Open();
                            adp.Fill(result);
                            cn.Close();
                        }
                        catch (Exception ex)
                        {
                            Logging.GetLog.ErrorLog(ex);
                        }
                    }
                }
            }
            return result;
        }
        public DataTable FillData(string Query, params SqlParameter[] prm)
        {
            DataTable result = new DataTable();
            using (SqlConnection cn = new SqlConnection(CnnString))
            {
                using (SqlCommand cmd = new SqlCommand(Query, cn))
                {
                    foreach (SqlParameter p in prm)
                        cmd.Parameters.Add(p);
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        try
                        {
                            cn.Open();
                            adp.Fill(result);
                            cmd.Parameters.Clear();
                            cn.Close();
                        }
                        catch (Exception ex)
                        {
                            Logging.GetLog.ErrorLog(ex);
                        }
                    }
                }
            }
            return result;
        }

        public SqlDataReader ReadData(string Query)
        {
            SqlDataReader rd = null;
            SqlConnection cn = new SqlConnection(CnnString);

            using (SqlCommand cmd = new SqlCommand(Query, cn))
            {
                try
                {
                    cn.Open();
                    rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    Logging.GetLog.ErrorLog(ex);
                }

            }
            return rd;
        }
        public SqlDataReader ReadData(string Query, params SqlParameter[] prm)
        {
            SqlDataReader result = null;
            SqlConnection cn = new SqlConnection(CnnString);
            using (SqlCommand cmd = new SqlCommand(Query, cn))
            {
                foreach (SqlParameter p in prm)
                    cmd.Parameters.Add(p);
                try
                {
                    cn.Open();
                    result = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    Logging.GetLog.ErrorLog(ex);
                }
            }
            return result;
        }

        

        public SqlTransaction OpenTransaction()
        {
            SqlConnection cn = new SqlConnection(CnnString);
            cn.Open();
            SqlTransaction trn = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
            return trn;
        }
    }

    public class ParameterCollection : List<SqlParameter>
    {
        List<string> paramNames = new List<string>();
        public ParameterCollection()
        {
            
        }
        public ParameterCollection(int capacity)
            : base(capacity)
        {
            
        }
        public ParameterCollection(IEnumerable<SqlParameter> collection)
            : base(collection)
        {
            
        }
                                                     
        public void AddItem(params SqlParameter[] parameters)
        {
            foreach (SqlParameter item in parameters)
                if (!paramNames.Contains(item.ParameterName))
                {
                    this.Add(item);
                    this.paramNames.Add(item.ParameterName);
                }
        }
    }

    public class Prm
    {
        public static SqlParameter Get(string paramName, SqlDbType type)
        {
            return new SqlParameter(paramName, type);
        }
        public static SqlParameter Get(string paramName, object value)
        {
            return new SqlParameter(paramName, value);
        }
        public static SqlParameter Get(string paramName, object value, SqlDbType type)
        {
            SqlParameter prm = new SqlParameter(paramName, type) { Value = value };
            return prm;
        }
        public static SqlParameter Get(string paramName, SqlDbType type, int size)
        {
            return new SqlParameter(paramName, type, size);
        }
        public static SqlParameter Get(string paramName, SqlDbType type, int size, object value)
        {
            SqlParameter prm = new SqlParameter(paramName, type, size) { Value = value };
            return prm;
        }
    }
}
